﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;

namespace Data.Database
{
    public class Horario
    {
        static private String cadenaConexion = ConfigurationManager.ConnectionStrings["Turnet"].ConnectionString; // @"Data Source=|DataDirectory|\BBDD.sdf";



        public DataSet ConseguirHorarios(DataSet dsprof, string fecha)
        {
            DataSet dsHora = new DataSet();

            try
            {

                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select H.id_horario,convert(varchar,H.horaincio,108),H.HoraFin FROM Horario AS H  WHERE H.ID_Horario NOT IN (SELECT H.ID_Horario FROM PROFESIONAL AS P INNER JOIN TURNOS AS T ON T.ID_PROFESIONAL=P.ID_PROFESIONAL INNER JOIN HORARIO AS H ON H.ID_HORARIO = T.ID_HORARIO AND P.Id_Profesional = '" + dsprof.Tables["Profesional"].Rows[0][0] + "' WHERE CONVERT(VARCHAR,T.Fecha,103) = CONVERT(VARCHAR,'" + fecha + "',103))";
                SqlDataAdapter daPac = new SqlDataAdapter(consulta, conexion);
                daPac.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daPac.Fill(dsHora, "Horario");
            }
            catch (Exception ex)
            {

            }


            return dsHora;




        }


        public DataSet ConseguirHorariosConHora(string fecha)
        {
            DataSet dsHora = new DataSet();

            try
            {

                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select H.id_horario FROM Horario AS H WHERE Convert(varchar,H.horaincio,108) ='" + fecha + "'";
                SqlDataAdapter daPac = new SqlDataAdapter(consulta, conexion);
                daPac.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daPac.Fill(dsHora, "Horario");
            }
            catch (Exception ex)
            {

            }


            return dsHora;





        }


    }
}
